$xlCellTypeLastCell = 11  # XlCellType常量(已用区域中的最后一个单元格) https://docs.microsoft.com/zh-CN/office/vba/api/Excel.Range.SpecialCells
$startRow = 1  #开始行

$IPcol = 1     #指定列 No.1
$UserCol = 2   #指定列 No.2
$PassCol = 3   #指定列 No.3

$excel = New-Object -Com Excel.Application
$wb = $excel.Workbooks.Open("$PSScriptRoot\hostPM.xlsx")

for ($i = 1; $i -le $wb.Sheets.Count; $i++)
{
    $sh = $wb.Sheets.Item($i)
    $endRow = $sh.UsedRange.SpecialCells($xlCellTypeLastCell).Row
    #Write-Host $endRow
    #$IP = $sh.Cells.Item($startRow, $col).Value2
    $IpRangeAddress = $sh.Cells.Item($startRow + 1, $IPcol).Address() + ":" + $sh.Cells.Item($endRow, $IPcol).Address()
    $UserRangeAddress = $sh.Cells.Item($startRow + 1, $UserCol).Address() + ":" + $sh.Cells.Item($endRow, $UserCol).Address()
    $PassRangeAddress = $sh.Cells.Item($startRow + 1, $PassCol).Address() + ":" + $sh.Cells.Item($endRow, $PassCol).Address()

        #Write-Host $IpRangeAddress
    #Write-Host $sh.Range($IpRangeAddress).Value2
    #$sh.Range($IpRangeAddress).Value2 | foreach{ Write-Host $_ }
    $IP = $sh.Range($IpRangeAddress).Value2    # .Net 2 dimensional array, element's index from [1,1] to [N,1]
    Write-Host $IP[1,1] $IP[2,1] $IP[3,1] $IP[4,1] $IP[5,1] $IP[6,1] $IP[7,1]

        #Write-Host $UserRangeAddress
    #Write-Host $sh.Range($UserRangeAddress).Value2
    #$sh.Range($UserRangeAddress).Value2 | foreach{ Write-Host $_ }
    $Username = $sh.Range($UserRangeAddress).Value2
    Write-Host $Username[1,1] $Username[2,1] $Username[3,1] $Username[4,1] $Username[5,1] $Username[6,1] $Username[7,1]

        #Write-Host $PassRangeAddress
    #Write-Host $sh.Range($PassRangeAddress).Value2
    #$sh.Range($PassRangeAddress).Value2 | foreach{ Write-Host $_ }
    $Password = $sh.Range($PassRangeAddress).Value2
    Write-Host $Password[1,1] $Password[2,1] $Password[3,1] $Password[4,1] $Password[5,1] $Password[6,1] $Password[7,1]

}

$excel.Workbooks.Close()